﻿<?xml version="1.0" encoding="UTF-8" ?>
<?arg urn:xmd-environment=Adoor.Metadata.XmdEnvironment, Adoor.Metadata ?>
<xsl:stylesheet version="1.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:typing="urn:xmd-typing"
	xmlns:mapping="urn:xmd-mapping"
	xmlns:log="urn:log"
	xmlns:env="urn:xmd-environment">
	
<xsl:output method="text"/>

<DBInput/>

<xsl:template name="GetClassUUID">
	<xsl:param name="className"/>
	<xsl:choose>
		<UUIDInput/>
		<xsl:otherwise>
			<xsl:message terminate="yes">uuidd for class '<xsl:value-of select="$className"/>' not defined</xsl:message>
		</xsl:otherwise>
	</xsl:choose>
</xsl:template>

<!--
<xsl:template match="@type[.='System.String']">
	<xsl:choose>
		<xsl:when test="../@length">varchar(<xsl:value-of select="../@length"/>) constraint [df_<xsl:value-of select="local-name(../..)"/>] not null CONSTRAINT [DF_Budget_Valeur_6477ECF3] DEFAULT default ''</xsl:when>
		<xsl:otherwise>text</xsl:otherwise>
	</xsl:choose>
</xsl:template>
<xsl:template match="@type[.='System.Boolean']">bit not null constraint [df_<xsl:value-of select="local-name(../..)"/>] default '0'</xsl:template>
<xsl:template match="@type[.='System.Int32']">int not null constraint [df_<xsl:value-of select="local-name(../..)"/>] default '0'</xsl:template>
<xsl:template match="@type[.='System.DateTime']">datetime not null constraint [df_<xsl:value-of select="local-name(../..)"/>] default '1800-01-01T00:00:00'</xsl:template>
<xsl:template match="@type[.='System.Decimal']">float not null constraint [df_<xsl:value-of select="local-name(../..)"/>] default '0'</xsl:template>
<xsl:template match="@type[.='System.Double']">float not null constraint [df_<xsl:value-of select="local-name(../..)"/>] default '0'</xsl:template>
<xsl:template match="@type[.='System.Single']">float not null constraint [df_<xsl:value-of select="local-name(../..)"/>] default '0'</xsl:template>
<xsl:template match="@type[.='System.Byte[]']">image</xsl:template>


<xsl:template match="@type">
	<xsl:message terminate="yes">datatype <xsl:value-of select="."/> not defined</xsl:message>
</xsl:template>-->

	
<xsl:template match="/">---- File: Delete.Adoor.DAL.<xsl:value-of select="local-name(/*[1])"/>.sql
	<xsl:variable name="entities" select="*/*[typing:entity[not(@abstract='true')]]"/>
	<xsl:variable name="references" select="//typing:reference"/>
	<xsl:variable name="SetTotal" select="env:SetProgressTotal(count($entities) * 3)" xmlns:env="urn:xmd-environment" />
	<xsl:apply-templates select="$entities" mode="delete"/>
	<xsl:apply-templates select="$references" mode="delete"/>
	
---- File: Create.Adoor.DAL.<xsl:value-of select="local-name(/*[1])"/>.sql

IF OBJECT_ID (N'dbo.dropdefaultconstraint') IS not NULL
	drop procedure dbo.dropdefaultconstraint
GO

CREATE PROCEDURE dbo.dropdefaultconstraint
    @tablename varchar(50),
    @column varchar(50)
as

DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname = (SELECT name 
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid 
WHERE object_name(so.parent_obj) = @tablename
AND so.xtype = 'D'
AND sc.colid = 
 (SELECT colid FROM syscolumns 
 WHERE id = object_id('dbo.' + @tablename) AND 
 name = @column))
SET @cmd = 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT '
+ @defname
EXEC(@cmd)

GO

IF OBJECT_ID (N'dbo.addColumnIfNotExists') IS not NULL
	drop procedure dbo.addColumnIfNotExists
GO
CREATE PROCEDURE dbo.addColumnIfNotExists
    @tablename varchar(50),
    @column varchar(50),
    @coldef varchar(200)
as

if not exists (SELECT  'Yes' AS Ex
FROM INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @tablename
AND  COLUMN_NAME = @column)
begin
declare @cmd varchar(2000)
set @cmd ='alter table [' + @tablename + '] add [' + @column +'] ' + @coldef
print @cmd
exec(@cmd)
end
GO

IF OBJECT_ID (N'dbo.changedefaultconstraint') IS not NULL
	drop procedure dbo.changedefaultconstraint
GO
CREATE PROCEDURE dbo.changedefaultconstraint
    @tablename varchar(50),
    @column varchar(50),
    @consname varchar(50),
    @coldef varchar(200)
as

if not exists(
SELECT name 
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid 
WHERE object_name(so.parent_obj) = @tablename
AND so.xtype = 'D'
and name = @consname
AND sc.colid = 
 (SELECT colid FROM syscolumns 
 WHERE id = object_id('dbo.' + @tablename) AND 
 name = @column) )
begin 
	exec dbo.dropdefaultconstraint @tablename, @column
declare @cmd varchar(1000)
set @cmd = 'alter table [' + @tablename + '] add CONSTRAINT [' + @consname +'] ' + @coldef + ' for [' +@column + ']'
print @cmd
	exec(@cmd)
end

GO
	
IF OBJECT_ID('dbo.DALTables', 'U') IS NULL
  CREATE TABLE dbo.DALTables(
	class varchar(255) not null,
	classId int not null,
	prefix varchar(10) not null CONSTRAINT [DF_ST_prefix] default '',
	PRIMARY KEY (class, classId)
)
GO

exec dbo.addColumnIfNotExists 'Adoor.DALTables', 'prefix', 'varchar(10) not null CONSTRAINT [DF_ST_prefix] default '''''
GO

if not exists (select * from sysindexes
  where id=object_id('Adoor.DALTables') 
    and name='class')
    CREATE INDEX [class] ON [Adoor.DALTables] (class)
GO

if not exists (select * from sysindexes
  where id=object_id('Adoor.DALTables') 
    and name='classId')
    CREATE INDEX [classId] ON [Adoor.DALTables] (class)
GO

if not exists (select * from sysindexes
  where id=object_id('Adoor.DALTables') 
    and name='prefix')
    CREATE INDEX [prefix] ON [Adoor.DALTables] (class)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

IF OBJECT_ID (N'dbo.cleFromOid') IS NOT NULL
   DROP function dbo.cleFromOid
GO

CREATE function dbo.cleFromOid(@oid uniqueidentifier)
RETURNS varchar(36)
AS
BEGIN
    RETURN(replace(substring(cast(@oid as varchar(36)), 1, 24), '-', ''))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



IF OBJECT_ID (N'dbo.newOid') IS NOT NULL
   DROP function dbo.newOid
GO

CREATE function dbo.newOid(@tableName varchar(255), @newid uniqueidentifier)
RETURNS uniqueidentifier
AS
BEGIN
	declare @oid varchar(50)	
	declare @classId int
	select @classId = classId from Adoor.DALTables where class = @tableName
	declare @tmp varchar(24)
	select @tmp = cast('000000000000' + CONVERT(char(12), @classId) as varchar(24))
	select @oid = cast(substring(cast(@newid as varchar(36)), 0, 25) + substring(@tmp, len(@tmp) - 11, 12) as uniqueidentifier)
    RETURN(@oid)
END
GO

IF OBJECT_ID (N'dbo.classFromOid') IS NOT NULL
   DROP function dbo.classFromOid
GO

CREATE function dbo.classFromOid(@oid uniqueidentifier)
RETURNS varchar(255)
AS
BEGIN
	declare @class varchar(255)
	select @class = class from Adoor.DALTables where classId = cast(substring(cast(@oid as varchar(36)),25, 12) as int)
    RETURN(@class)
END
GO

IF OBJECT_ID (N'dbo.oidFromCle') IS NOT NULL
   DROP function dbo.oidFromCle
GO

CREATE function dbo.oidFromCle(@tableName varchar(255), @cle decimal)
RETURNS varchar(36)
AS
BEGIN
	declare @oid uniqueidentifier	
	declare @classId int
	select @classId = classId from Adoor.DALTables where class = @tableName

	declare @sign char(1)
	select @sign = case when @cle >= 0 then '0' else 'F' end	

	declare @scle varchar(40)
	select @scle = cast('00000000000000000000' + CONVERT(char(20), @cle) as varchar(40))
	select @scle = substring(@scle, len(@scle) - 19, 20)

	declare @tmpid varchar(24)
	select @tmpid =  cast('000000000000' + CAST(@classId as char(12)) as char(24))
	
	declare @tmp varchar(36)
	select @tmp = @sign + substring(@scle, 2, 7) 
		+ '-' + substring(@scle, 9, 4)
		+ '-' + substring(@scle, 13, 4)
		+ '-' + substring(@scle, 17, 4)
		+ '-' + substring(@tmpid, len(@tmpid) - 11, 12)

	select @oid = cast(@tmp as uniqueidentifier)
    RETURN(@oid)
END
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
	
	<xsl:apply-templates  select="$entities" mode="create"/>
	<xsl:apply-templates select="$references" mode="create"/>

	<xsl:apply-templates select="$entities" mode="insert"/>
	
---- File: Index.Adoor.DAL.<xsl:value-of select="local-name(/*[1])"/>.sql
	<xsl:apply-templates  select="$entities" mode="index"/>	
</xsl:template>

<xsl:template match="*[typing:entity]" mode="delete">
IF not (OBJECT_ID('dbo.<xsl:value-of select="env:TypeName(typing:entity/@type)"/>', 'U') IS NULL)
	drop table [dbo].[<xsl:value-of select="env:TypeName(typing:entity/@type)"/>] 
GO
</xsl:template>

<xsl:template match="*[typing:entity]" mode="create">

<xsl:variable name="className" select="env:TypeName(typing:entity/@type)" xmlns:env="urn:xmd-environment"/>
<xsl:variable name="classId"><xsl:call-template name="GetClassUUID"><xsl:with-param name="className" select="$className"/></xsl:call-template></xsl:variable>
<xsl:variable name="log" select="env:Log(concat('Processing type ', className),'0')" xmlns:env="urn:xmd-environment"/>
<xsl:variable name="progress" select="env:UpdateProgress()" xmlns:env="urn:xmd-environment"/>
<!--<xsl:message>entity <xsl:value-of select="className"/></xsl:message>-->

<xsl:if test="not(typing:entity/@abstract='true')">
IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '<xsl:value-of select="$className"/>')

CREATE TABLE [dbo].[<xsl:value-of select="$className"/>] (
	[OID] uniqueidentifier NOT NULL CONSTRAINT [DF_<xsl:value-of select="$classId"/>_OID] default '00000000-0000-0000-0000-000000000000'<xsl:for-each select="*[typing:property | typing:reference]"><xsl:apply-templates select="." mode="column"/>
	</xsl:for-each>
	,PRIMARY KEY  ([OID])
)

GO

</xsl:if>
</xsl:template>

<xsl:template match="*[typing:entity]" mode="delete">
<xsl:variable name="className" select="env:TypeName(typing:entity/@type)" xmlns:env="urn:xmd-environment"/>
<xsl:variable name="classId"><xsl:call-template name="GetClassUUID"><xsl:with-param name="className" select="$className"/></xsl:call-template></xsl:variable>
<xsl:variable name="log" select="env:Log(concat('Processing type ', className),'0')" xmlns:env="urn:xmd-environment"/>
<xsl:variable name="progress" select="env:UpdateProgress()" xmlns:env="urn:xmd-environment"/>
<!--<xsl:message>entity <xsl:value-of select="className"/></xsl:message>-->

<xsl:if test="not(typing:entity/@abstract='true')">
IF not (OBJECT_ID('dbo.<xsl:value-of select="$className"/>', 'U') IS NULL)
	drop table [dbo].[<xsl:value-of select="$className"/>] 
GO

</xsl:if>
</xsl:template>

<xsl:template match="*[typing:entity]" mode="index">
<xsl:variable name="className" select="env:TypeName(typing:entity/@type)" xmlns:env="urn:xmd-environment"/>
<xsl:variable name="classId"><xsl:call-template name="GetClassUUID"><xsl:with-param name="className" select="$className"/></xsl:call-template></xsl:variable>
<xsl:if test="not(typing:entity/@abstract='true')">
<xsl:for-each select="*[typing:property | typing:reference]"><xsl:apply-templates select="." mode="indexcolumn"><xsl:with-param name="classId" select="$classId"/></xsl:apply-templates></xsl:for-each>
</xsl:if>
</xsl:template>

<xsl:template match="*[typing:entity]" mode="insert">
<xsl:variable name="className" select="env:TypeName(typing:entity/@type)" xmlns:env="urn:xmd-environment"/>
<xsl:variable name="classId"><xsl:call-template name="GetClassUUID"><xsl:with-param name="className" select="$className"/></xsl:call-template></xsl:variable>

if exists ( select * from Adoor.DALTables where class = '<xsl:value-of select="$className"/>')
	update Adoor.DALTables set classId = <xsl:value-of select="$classId"/> where class = '<xsl:value-of select="$className"/>'
GO
if not exists ( select * from Adoor.DALTables where class = '<xsl:value-of select="$className"/>')
	insert into Adoor.DALTables (class, classId) values ('<xsl:value-of select="$className"/>', <xsl:value-of select="$classId"/>);
GO

</xsl:template>

<xsl:template match="typing:property" mode="column">
	<xsl:variable name="className" select="env:TypeName(../../typing:entity/@type)" xmlns:env="urn:xmd-environment"/>
	<xsl:variable name="classId"><xsl:call-template name="GetClassUUID"><xsl:with-param name="className" select="$className"/></xsl:call-template></xsl:variable>	
	<xsl:variable name="type"><xsl:apply-templates select="@type"><xsl:with-param name="classId" select="$classId"/></xsl:apply-templates></xsl:variable>
	<xsl:variable name="name"><xsl:value-of select="local-name(..)"/></xsl:variable>
	,[<xsl:value-of select="$name"/>] <xsl:choose>
	<xsl:when test="$name = 'cle'"> INT IDENTITY(1,1) NOT FOR REPLICATION</xsl:when>
	<xsl:when test="$name = 'Cle'"> INT IDENTITY(1,1) NOT FOR REPLICATION</xsl:when>
	<xsl:otherwise>	<xsl:value-of select="$type"/></xsl:otherwise>
	</xsl:choose></xsl:template>
	
	<xsl:template match="typing:reference[@multiplicity = '1']" mode="column">
			<xsl:variable name="back-role">
				<xsl:value-of select="@parent" />
			</xsl:variable>
			<xsl:variable name="tableName" select="env:TableName($back-role, local-name(..))" />
			<xsl:variable name="test" select="starts-with($tableName, local-name(..))" />
			<!--multiplicity <xsl:value-of select="@multiplicity"/> <xsl:value-of select="@adjacentMultiplicity"/>-->
			<xsl:choose>
				<xsl:when test="@adjacentMultiplicity = '1'">
						<xsl:if test="boolean($test) or @navigable = 'false'">
							<!--<xsl:message>map </xsl:message>
							<xsl:message>table name = <xsl:value-of select="$tableName"/> </xsl:message>
							<xsl:message>back-role = <xsl:value-of select="$back-role"/> </xsl:message>
							<xsl:message>local-name = <xsl:value-of select="local-name(..)"/> </xsl:message>-->
	,[<xsl:value-of select="local-name(..)"/>] uniqueidentifier</xsl:if></xsl:when>
				<xsl:otherwise>
	,[<xsl:value-of select="local-name(..)"/>] uniqueidentifier</xsl:otherwise>
			</xsl:choose>		
	</xsl:template>
	
	<xsl:template match="typing:reference[@multiplicity = '1']" mode="indexcolumn">
		<xsl:param name="classId"/>
<!--multiplicity <xsl:value-of select="@multiplicity"/> <xsl:value-of select="@adjacentMultiplicity"/>-->
			<xsl:variable name="back-role">
				<xsl:value-of select="@parent" />
			</xsl:variable>
			<xsl:variable name="table">
				<xsl:value-of select="local-name(../..)"/>
				<!--<xsl:choose>
				<xsl:when test="@parent = local-name(../..)"><xsl:value-of select="@parent"/></xsl:when>
				<xsl:when test="../../typing:entity/@parentEntity"><xsl:value-of select="../../typing:entity/@parentEntity"/></xsl:when>
				<xsl:otherwise><xsl:value-of select="local-name(../..)"/></xsl:otherwise>
			</xsl:choose>-->
			</xsl:variable>
			<xsl:variable name="tableName" select="env:TableName($back-role, local-name(..))" />
			<xsl:variable name="test" select="starts-with($tableName, local-name(..))" />
			<xsl:choose>
				<xsl:when test="@adjacentMultiplicity = '1'">
						<xsl:if test="boolean($test) or @navigable = 'false'">
if not exists (select * from sysindexes
  where id=object_id('<xsl:value-of select="$table"/>') 
    and name='<xsl:value-of select="local-name(..)"/>')
    CREATE INDEX [<xsl:value-of select="local-name(..)"/>] ON [<xsl:value-of select="$table"/>] ([<xsl:value-of select="local-name(..)"/>])
GO
						</xsl:if>
				</xsl:when>
				<xsl:otherwise>
if not exists (select * from sysindexes
  where id=object_id('<xsl:value-of select="$table"/>') 
    and name='<xsl:value-of select="local-name(..)"/>')
    CREATE INDEX [<xsl:value-of select="local-name(..)"/>] ON [<xsl:value-of select="$table"/>] ([<xsl:value-of select="local-name(..)"/>])
GO
				</xsl:otherwise>
			</xsl:choose>		
	</xsl:template>
	
	<xsl:template match="typing:reference[@multiplicity = '*']" mode="create">
<!-- multiplicity <xsl:value-of select="@multiplicity"/> <xsl:value-of select="@adjacentMultiplicity"/>-->
			<xsl:choose>
				<!--<xsl:when test="count(../../typing:entity/@parent) = 1 or @adjacentMultiplicity = '*'" >-->
				<xsl:when test="@navigable = 'false' or @adjacentMultiplicity = '*'">
					<xsl:variable name="back-role">
						<xsl:choose>
							<xsl:when test="@parent">
								<xsl:value-of select="@parent" />
							</xsl:when>
							<xsl:otherwise>
								<xsl:value-of select="local-name(../..)" />
							</xsl:otherwise>
						</xsl:choose>
					</xsl:variable>
					<xsl:variable name="tableName" select="env:TableName($back-role, local-name(..))" xmlns:env="urn:xmd-environment" />
IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '<xsl:value-of select="$tableName"/>')

CREATE TABLE [dbo].[<xsl:value-of select="$tableName"/>] (
	[<xsl:value-of select="$back-role"/>] uniqueidentifier,
	[<xsl:value-of select="local-name(..)"/>] uniqueidentifier
	,PRIMARY KEY ([<xsl:value-of select="$back-role"/>], [<xsl:value-of select="local-name(..)"/>])
)
GO
				</xsl:when>
				<xsl:otherwise></xsl:otherwise>
			</xsl:choose>
	</xsl:template>

<xsl:template match="typing:reference[@multiplicity = '*']" mode="indexcolumn">
<!-- multiplicity <xsl:value-of select="@multiplicity"/> <xsl:value-of select="@adjacentMultiplicity"/>-->
			<xsl:choose>
				<!--<xsl:when test="count(../../typing:entity/@parent) = 1 or @adjacentMultiplicity = '*'" >-->
				<xsl:when test="@navigable = 'false' or @adjacentMultiplicity = '*'">
					<xsl:variable name="back-role">
						<xsl:choose>
							<xsl:when test="@parent">
								<xsl:value-of select="@parent" />
							</xsl:when>
							<xsl:otherwise>
								<xsl:value-of select="local-name(../..)" />
							</xsl:otherwise>
						</xsl:choose>
					</xsl:variable>
					<xsl:variable name="tableName" select="env:TableName($back-role, local-name(..))" xmlns:env="urn:xmd-environment" />
if not exists (select * from sysindexes
  where id=object_id('<xsl:value-of select="$tableName"/>') 
    and name='<xsl:value-of select="local-name(..)"/>')
    CREATE INDEX [<xsl:value-of select="local-name(..)"/>] ON [<xsl:value-of select="$tableName"/>] ([<xsl:value-of select="local-name(..)"/>])
GO

if not exists (select * from sysindexes
  where id=object_id('<xsl:value-of select="$tableName"/>') 
    and name='<xsl:value-of select="$back-role"/>')
    CREATE INDEX [<xsl:value-of select="$back-role"/>] ON [<xsl:value-of select="$tableName"/>] ([<xsl:value-of select="$back-role"/>])
GO
				</xsl:when>
				<xsl:otherwise></xsl:otherwise>
			</xsl:choose>
	</xsl:template>

</xsl:stylesheet>